Imports System
Imports System.Data
Imports System.Data.Common
Imports System.Collections.Generic
Imports Microsoft.Practices.EnterpriseLibrary.Data

Namespace DAL.Servicio
    Public Class Reserva
        Public Function Reservar(ByVal _Reserva As BEL.Servicio.Reserva, ByVal _Parametros As Object()) As System.Boolean
            Dim db As Database = DatabaseFactory.CreateDatabase("ServCons")
            Dim cm As DbCommand = db.GetStoredProcCommand("[SER_I_Reserva]")
            Using cn As DbConnection = db.CreateConnection()
                cn.Open()
                Dim tx As DbTransaction = cn.BeginTransaction()
                Try
                    db.AddInParameter(cm, "CO_NATURAL_SOCIO", DbType.Int32, _Reserva.TITULAR.CO_NATURAL)
                    db.AddInParameter(cm, "FE_HORAINICIO", DbType.DateTime, _Reserva.FE_HORAINICIO)
                    db.AddInParameter(cm, "FE_HORAFIN", DbType.DateTime, _Reserva.FE_HORAFIN)
                    db.AddInParameter(cm, "CO_PLAN", DbType.Int32, Convert.ToInt32(_Parametros(0)))
                    db.AddInParameter(cm, "CO_SERVICIO", DbType.Int32, Convert.ToInt32(_Parametros(1)))
                    db.AddInParameter(cm, "CO_ITEM", DbType.Int32, Convert.ToInt32(_Parametros(2)))
                    db.AddInParameter(cm, "CO_NATURAL_PERSONAL", DbType.Int32, Convert.ToInt32(_Parametros(3)))
                    db.ExecuteNonQuery(cm, tx)

                    tx.Commit()
                Catch _ex As Exception
                    tx.Rollback()
                    Throw New Exception(_ex.Message, _ex.InnerException)
                Finally
                    tx.Dispose()
                    cm.Dispose()
                    tx = Nothing
                    cm = Nothing
                    db = Nothing
                End Try
            End Using
            Return True
        End Function
		Public Function Imprimir(ByVal _Parametros As Object()) As DataTable
            Dim ds As New DataSet()
            Dim db As Database = DatabaseFactory.CreateDatabase()
            Dim cm As System.Data.Common.DbCommand = db.GetStoredProcCommand("[SER_S_Reserva_Reporte]")
            db.AddInParameter(cm, "NO_DOCIDENTIDAD", DbType.String, Convert.ToString(_Parametros(0)))
            db.AddInParameter(cm, "CO_SERVICIO", DbType.Int32, Convert.ToInt32(_Parametros(1)))
            db.AddInParameter(cm, "FE_INICIO", DbType.DateTime, Convert.ToDateTime(_Parametros(2)))
            db.AddInParameter(cm, "FE_FIN", DbType.DateTime, Convert.ToDateTime(_Parametros(3)))
            Try
                ds = db.ExecuteDataSet(cm)
                ds.Tables(0).TableName = "REPORTE"
                Return ds.Tables(0)
            Catch ex As System.Data.SqlClient.SqlException
                Throw New Exception(ex.Message, ex.InnerException)
            End Try
        End Function
        Public Function Listar(ByVal _Parametros As Object()) As DataTable
            Dim ds As New DataSet()
            Dim db As Database = DatabaseFactory.CreateDatabase()
            Dim cm As System.Data.Common.DbCommand = db.GetStoredProcCommand("[SER_S_Reserva_Listado]")
            db.AddInParameter(cm, "CO_SERVICIO", DbType.Int32, Convert.ToInt32(_Parametros(0)))
            db.AddInParameter(cm, "CO_NATURAL", DbType.Int32, Convert.ToInt32(_Parametros(1)))
            db.AddInParameter(cm, "FL_SEXO", DbType.String, Convert.ToString(_Parametros(2)))

            Try
                ds = db.ExecuteDataSet(cm)
                ds.Tables(0).TableName = "SERVICIOS"
                Return ds.Tables(0)
            Catch ex As System.Data.SqlClient.SqlException
                Throw New Exception(ex.Message, ex.InnerException)
            End Try
        End Function
        Public Function ListarPersonal() As DataTable
            Dim ds As New DataSet()
            Dim db As Database = DatabaseFactory.CreateDatabase()
            Dim cm As System.Data.Common.DbCommand = db.GetStoredProcCommand("[SER_S_Reserva_Personal]")

            Try
                ds = db.ExecuteDataSet(cm)
                ds.Tables(0).TableName = "PERSONAL"

                Return ds.Tables(0)
            Catch ex As System.Data.SqlClient.SqlException
                Throw New Exception(ex.Message, ex.InnerException)
            End Try
        End Function
        Public Function ListarServicios() As DataTable
            Dim ds As New DataSet()
            Dim db As Database = DatabaseFactory.CreateDatabase()
            Dim cm As System.Data.Common.DbCommand = db.GetStoredProcCommand("[SER_S_Reserva_Servicios]")

            Try
                ds = db.ExecuteDataSet(cm)
                ds.Tables(0).TableName = "SERVICIOS"

                Return ds.Tables(0)
            Catch ex As System.Data.SqlClient.SqlException
                Throw New Exception(ex.Message, ex.InnerException)
            End Try
        End Function

		Public Function ValidaCapacidad(ByVal _Parametros() As Object) As Boolean
			Dim intRpta As Integer
			Dim db As Database = DatabaseFactory.CreateDatabase("ServCons")
			Dim cm As DbCommand = db.GetStoredProcCommand("[SER_S_Reserva_ValidaCapacidad]")
			Using cn As DbConnection = db.CreateConnection()
				cn.Open()
				Dim tx As DbTransaction = cn.BeginTransaction()
				Try
					db.AddInParameter(cm, "FE_RESERVAINI", DbType.DateTime, Convert.ToDateTime(_Parametros(0)))
					db.AddInParameter(cm, "FE_RESERVAFIN", DbType.DateTime, Convert.ToDateTime(_Parametros(1)))
					db.AddInParameter(cm, "Co_Plan", DbType.Int32, Convert.ToInt32(_Parametros(2)))
					db.AddInParameter(cm, "Co_Servicio", DbType.Int32, Convert.ToInt32(_Parametros(3)))
					db.AddInParameter(cm, "Co_Item", DbType.Int32, Convert.ToInt32(_Parametros(4)))
					db.AddInParameter(cm, "Co_Natural", DbType.Int32, Convert.ToInt32(_Parametros(5)))
					Using dr As IDataReader = db.ExecuteReader(cm, tx)
						dr.Read()
						intRpta = Convert.ToInt32(dr("EXISTE"))
					End Using
					tx.Commit()
				Catch _ex As Exception
					tx.Rollback()
					Throw New Exception(_ex.Message, _ex.InnerException)
				Finally
					tx.Dispose()
					cm.Dispose()
					tx = Nothing
					cm = Nothing
					db = Nothing
				End Try
			End Using
			If intRpta > 0 Then
				Return True
			Else
				Return False
			End If
		End Function
		Public Function ValidaSocio(ByVal _Parametros() As Object) As Boolean
			Dim intRpta As Integer
			Dim db As Database = DatabaseFactory.CreateDatabase("ServCons")
			Dim cm As DbCommand = db.GetStoredProcCommand("[SER_S_Reserva_ValidaSocio]")
			Using cn As DbConnection = db.CreateConnection()
				cn.Open()
				Dim tx As DbTransaction = cn.BeginTransaction()
				Try
					db.AddInParameter(cm, "Co_Plan", DbType.Int32, Convert.ToInt32(_Parametros(0)))
					db.AddInParameter(cm, "Co_Servicio", DbType.Int32, Convert.ToInt32(_Parametros(1)))
					db.AddInParameter(cm, "Co_Item", DbType.Int32, Convert.ToInt32(_Parametros(2)))
					db.AddInParameter(cm, "Co_Natural", DbType.Int32, Convert.ToInt32(_Parametros(3)))
					Using dr As IDataReader = db.ExecuteReader(cm, tx)
						dr.Read()
						intRpta = Convert.ToInt32(dr("EXISTE"))
					End Using
					tx.Commit()
				Catch _ex As Exception
					tx.Rollback()
					Throw New Exception(_ex.Message, _ex.InnerException)
				Finally
					tx.Dispose()
					cm.Dispose()
					tx = Nothing
					cm = Nothing
					db = Nothing
				End Try
			End Using
			If intRpta > 0 Then
				Return False
			Else
				Return True
			End If
		End Function
	End Class
End Namespace